package com.starbaba.stat.service.impl;

import java.util.List;
import java.util.Map;

import org.apache.commons.lang3.StringUtils;
import org.springframework.stereotype.Component;

import com.base.common.database.BaseDao;
import com.starbaba.core.web.page.PageResponse;
import com.starbaba.stat.model.StatNewUserModel;
import com.starbaba.stat.service.StatNewUserService;
import com.starbaba.stat.utils.Constant;

@SuppressWarnings("rawtypes")
@Component
public class StatNewUserServiceImpl implements StatNewUserService {

	static BaseDao dao = new BaseDao(Constant.LOAN_STAT_DB_POOL);// Constant.LOAN_STAT_DB_POOL
	
	@Override
	public void showPage(Map<String, Object> paramsMap) throws Exception {
		
		List<StatNewUserModel> prdList = dao.list("select distinct a.app_prd_id prd_id, b.name prd_name from stat.stat_app_new a, loan.comm_product_prdid b where a.app_prd_id = b.prdid ", StatNewUserModel.class);
		List<StatNewUserModel> channelList = dao.list("select distinct a.channel, b.channel_name channel_name from stat.stat_app_new a, loan.comm_product_channel b where a.channel = b.channel_code", StatNewUserModel.class);
		
		StatNewUserModel all = new StatNewUserModel();
		all.setPrd_id("all");
		all.setChannel("all");
		all.setPrd_name("全部");
		all.setChannel_name("全部");
		
		prdList.add(0, all);
		channelList.add(0, all);
		
		paramsMap.put("prdList", prdList);
		paramsMap.put("channelList", channelList);

	}

	@Override
	public List getList(Integer start, Integer length, PageResponse pageResponse, Map<String, Object> paramsMap) throws Exception {
		
		String startTime = (String) paramsMap.get("startTime");
		String endTime = (String) paramsMap.get("endTime");
		String platform = (String) paramsMap.get("platform");
		String prd_id = (String) paramsMap.get("prd_id");
		String channel = (String) paramsMap.get("channel");
		
		StringBuilder whereParams = new StringBuilder();
		if (StringUtils.isNotEmpty(startTime)) {
			whereParams.append("and date >= '" + startTime + "' ");
		}
		if (StringUtils.isNotEmpty(endTime)) {
			whereParams.append("and date <= '" + endTime + "' ");
		}
		if (StringUtils.isNotEmpty(platform) && !"all".equals(platform)) {
			whereParams.append("and platform = '" + platform + "' ");
		}
		if (StringUtils.isNotEmpty(prd_id) && !"all".equals(prd_id)) {
			whereParams.append("and app_prd_id = '" + prd_id + "' ");
		}
		if (StringUtils.isNotEmpty(channel) && !"all".equals(channel)) {
			whereParams.append("and channel = '" + channel + "' ");
		}
		
		StringBuilder groupBy = new StringBuilder();
		groupBy.append("group by date, ");
		if (!"all".equals(platform)) {
			groupBy.append("platform, ");
		}
		if (!"all".equals(prd_id)) {
			groupBy.append("app_prd_id, ");
		}
		if (!"all".equals(channel)) {
			groupBy.append("channel, ");
		}
		String groupByString = groupBy.toString().substring(0, groupBy.length() - 2) + " ";

		StringBuilder sqlAllUv = new StringBuilder("SELECT a.date, count(a.phone_id) count, a.app_prd_id prd_id, a.channel, a.platform, ");
		sqlAllUv.append("cpp.name prd_name, cpc.channel_name channel_name ");
		sqlAllUv.append("FROM stat_app_new a left join loan.comm_product_prdid cpp on a.app_prd_id = cpp.prdid ");
		sqlAllUv.append("left join loan.comm_product_channel cpc on a.channel = cpc.channel_code WHERE 1=1 ");
		sqlAllUv.append(whereParams).append(groupByString).append("order by time desc ");
		
		String sqlCount = "select count(1) from (" + sqlAllUv.toString() + ") b ";
		if (start != null && length != null) {
			sqlAllUv.append("limit " + start + ", " + length);
		}
		
		int count = dao.getInt(sqlCount);
		List<StatNewUserModel> list = dao.list(sqlAllUv.toString(), StatNewUserModel.class);
		for (StatNewUserModel model : list) {
			if (StringUtils.isEmpty(model.getPlatform())) {
				model.setPlatform("未知");
			}
			if (StringUtils.isEmpty(model.getPrd_name())) {
				model.setPrd_name("未知");
			}
			if (StringUtils.isEmpty(model.getChannel_name())) {
				model.setChannel_name("未知");
			}
			
			if ("all".equals(platform)) {
				model.setPlatform("全部");
			}
			if ("all".equals(prd_id)) {
				model.setPrd_name("全部");
			}
			if ("all".equals(channel)) {
				model.setChannel_name("全部");
			}
		}
		pageResponse.setITotalRecords(count);
		pageResponse.setITotalDisplayRecords(count);
		
		return list;
	}
	

	public List<StatNewUserModel> getExcelList(Map<String, Object> paramsMap) throws Exception {
		
		String startTime = (String) paramsMap.get("startTime");
		String endTime = (String) paramsMap.get("endTime");
		String platform = (String) paramsMap.get("platform");
		String prd_id = (String) paramsMap.get("prd_id");
		String channel = (String) paramsMap.get("channel");
		
		StringBuilder whereParams = new StringBuilder();
		if (StringUtils.isNotEmpty(startTime)) {
			whereParams.append("and date >= '" + startTime + "' ");
		}
		if (StringUtils.isNotEmpty(endTime)) {
			whereParams.append("and date <= '" + endTime + "' ");
		}
		if (StringUtils.isNotEmpty(platform) && !"all".equals(platform)) {
			whereParams.append("and platform = '" + platform + "' ");
		}
		if (StringUtils.isNotEmpty(prd_id) && !"all".equals(prd_id)) {
			whereParams.append("and app_prd_id = '" + prd_id + "' ");
		}
		if (StringUtils.isNotEmpty(channel) && !"all".equals(channel)) {
			whereParams.append("and channel = '" + channel + "' ");
		}
		
		StringBuilder groupBy = new StringBuilder();
		groupBy.append("group by date, ");
		if (!"all".equals(platform)) {
			groupBy.append("platform, ");
		}
		if (!"all".equals(prd_id)) {
			groupBy.append("app_prd_id, ");
		}
		if (!"all".equals(channel)) {
			groupBy.append("channel, ");
		}
		String groupByString = groupBy.toString().substring(0, groupBy.length() - 2) + " ";

		StringBuilder sqlAllUv = new StringBuilder("SELECT a.date, count(a.phone_id) count, a.app_prd_id prd_id, a.channel, a.platform, ");
		sqlAllUv.append("cpp.name prd_name, cpc.channel_name channel_name ");
		sqlAllUv.append("FROM stat_app_new a left join loan.comm_product_prdid cpp on a.app_prd_id = cpp.prdid ");
		sqlAllUv.append("left join loan.comm_product_channel cpc on a.channel = cpc.channel_code WHERE 1=1 ");
		sqlAllUv.append(whereParams).append(groupByString).append("order by time desc ");
		
		List<StatNewUserModel> list = dao.list(sqlAllUv.toString(), StatNewUserModel.class);
		for (StatNewUserModel model : list) {
			if (StringUtils.isEmpty(model.getPlatform())) {
				model.setPlatform("未知");
			}
			if (StringUtils.isEmpty(model.getPrd_name())) {
				model.setPrd_name("未知");
			}
			if (StringUtils.isEmpty(model.getChannel_name())) {
				model.setChannel_name("未知");
			}
			
			if ("all".equals(platform)) {
				model.setPlatform("全部");
			}
			if ("all".equals(prd_id)) {
				model.setPrd_name("全部");
			}
			if ("all".equals(channel)) {
				model.setChannel_name("全部");
			}
		}
		return list;
	}
	
}